﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class RoomTypeRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();

        public RoomTypeRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
        }
        public void Insert(RoomType unit)
        {
            string insertSQL = String.Format(@"insert into table_roomtype 
                (
                    roomtype_code,
                    roomtype_name,
                    roomtype_facility,
                    roomtype_image,
                    unit_id) 
                VALUES ('{0}','{1}','{2}','{3}',{4})",
                unit.CODE, unit.NAME, unit.FACILITY, unit.IMAGE, unit.UNIT.ID);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            unit.ID = GetMaximumId();
        }
        public RoomType GetById(int id)
        {
            string getByID = String.Format("select * from table_roomtype where roomtype_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            RoomType unit = transform(aReader);
            aReader.Close();
            return unit;
        }
        public void Update(RoomType unit)
        {
            string updateSQL = String.Format(@"update table_roomtype set 
                roomtype_code = '{0}', 
                roomtype_name='{1}',
                roomtype_facility='{2}',
                roomtype_image='{3}',
                unit_id = {4}
                where roomtype_id = {5}",
                unit.CODE, unit.NAME, unit.FACILITY, unit.IMAGE, unit.UNIT.ID, unit.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public void Delete(RoomType unit)
        {
            string deleteSQL = "delete from table_roomtype where roomtype_id = " + unit.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(roomtype_id) from table_roomtype");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_roomtype where unit_id = {0}", unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int unit)
        {
            string sql = String.Format("select * from table_roomtype where unit_id = {0}", unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        private RoomType transform(MySqlDataReader aReader)
        {
            RoomType unit = null;
            while (aReader.Read())
            {
                unit = new RoomType();
                unit.ID = Convert.ToInt32(aReader["roomtype_id"]);
                unit.CODE = aReader["roomtype_code"].ToString();
                unit.NAME = aReader["roomtype_name"].ToString();
                unit.FACILITY = aReader["roomtype_facility"].ToString();
                unit.IMAGE = aReader["roomtype_image"].ToString();
                unit.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
            }
            return unit;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                RoomType unit = new RoomType();
                unit.ID = Convert.ToInt32(aReader["roomtype_id"]);
                unit.CODE = aReader["roomtype_code"].ToString();
                unit.NAME = aReader["roomtype_name"].ToString();
                unit.FACILITY = aReader["roomtype_facility"].ToString();
                unit.IMAGE = aReader["roomtype_image"].ToString();
                unit.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                result.Add(unit);
            }
            return result;
        }
    }
}
